﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web;

namespace MainWebService
{
    public class DAO
    {

        private SqlConnection _con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ToString());
        public SqlConnection Connection
        {

            get
            {
                return _con;
            }
        }
        public DAO()
        {

        }
        public int ExecuteQuery(string query)
        {
            int result = 0;
            SqlCommand cmd = PrepareCommand(query, CommandType.Text);
            Connection.Open();
            result = cmd.ExecuteNonQuery();
            Connection.Close();
            return result;
        }
        public int ExecuteQuery(string procName, List<SqlParameter> sqlParams)
        {
            int result = 0;
            SqlCommand cmd = PrepareCommand(procName, CommandType.StoredProcedure);
            AddParameters(cmd, sqlParams);
            Connection.Open();
            result = cmd.ExecuteNonQuery();
            Connection.Close();
            return result;
        }
        public int ExecuteQuery(string procName, List<SqlParameter> sqlParams, ref object outPutValue)
        {
            int result = 0;
            SqlCommand cmd = PrepareCommand(procName, CommandType.StoredProcedure);
            AddParameters(cmd, sqlParams);
            Connection.Open();
            result = cmd.ExecuteNonQuery();
            try
            {
                outPutValue = cmd.Parameters[sqlParams.Count - 1].Value;
            }
            catch { outPutValue = null; }
            Connection.Close();
            return result;
        }

        public int ExecuteQuery(string procName, List<SqlParameter> sqlParams, ref object outPutValue1, ref object outputValue2)
        {
            int result = 0;
            SqlCommand cmd = PrepareCommand(procName, CommandType.StoredProcedure);
            AddParameters(cmd, sqlParams);
            Connection.Open();
            result = cmd.ExecuteNonQuery();
            try
            {
                outPutValue1 = cmd.Parameters[sqlParams.Count - 2].Value;
                outputValue2 = cmd.Parameters[sqlParams.Count - 1].Value;
            }
            catch { outPutValue1 = null; outputValue2 = null; }
            Connection.Close();
            return result;
        }

        public Object ExecuteScalar(string query)
        {
            SqlCommand cmd = PrepareCommand(query, CommandType.Text);
            Connection.Open();
            Object obj = cmd.ExecuteScalar();
            Connection.Close();
            return obj;

        }
        public Object ExecuteScalar(string procName, List<SqlParameter> sqlParams)
        {
            SqlCommand cmd = PrepareCommand(procName, CommandType.StoredProcedure);
            AddParameters(cmd, sqlParams);
            Connection.Open();
            object obj = cmd.ExecuteScalar();
            Connection.Close();
            return obj;
        }
        public DataSet ExecuteDataSet(string query)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter da = null;
            SqlCommand cmd = PrepareCommand(query, CommandType.Text);
            da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            return ds;
        }
        public DataSet ExecuteDataSet(string procName, List<SqlParameter> sqlParams)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter da = null;
            SqlCommand cmd = PrepareCommand(procName, CommandType.StoredProcedure);
            AddParameters(cmd, sqlParams);
            da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            return ds;
        }
        public DataSet ExecuteDataSet(string procName, List<SqlParameter> sqlParams, ref object outPutValue)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter da = null;
            SqlCommand cmd = PrepareCommand(procName, CommandType.StoredProcedure);
            AddParameters(cmd, sqlParams);
            da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            try
            {
                outPutValue = cmd.Parameters[sqlParams.Count - 1].Value;
            }
            catch { outPutValue = null; }
            return ds;
        }

        public string ExecuteString(string query)
        {
            
            DataSet ds = new DataSet();
            SqlDataAdapter da = null;
            SqlCommand cmd = PrepareCommand(query, CommandType.Text);
            da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            return GetStringFromDataSet(ds);
        }
        public string ExecuteString(string procName, List<SqlParameter> sqlParams)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter da = null;
            SqlCommand cmd = PrepareCommand(procName, CommandType.StoredProcedure);
            AddParameters(cmd, sqlParams);
            da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            return GetStringFromDataSet(ds);
        }
        private static string GetStringFromDataSet(DataSet ds)
        {
            StringBuilder sb = new StringBuilder();
            if (Utility.IsValidDataSet(ds))
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                    {
                        sb.Append(ds.Tables[0].Rows[i][j].ToString());
                        sb.Append(",");
                    }
                }
                if (sb.Length > 0)
                    sb.Remove(sb.Length - 1, 1);
            }
            return sb.ToString();
        }
        public SqlParameter AddParameter(string paramName, object paramValue)
        {
            return new SqlParameter(paramName, paramValue);
        }
        public SqlParameter AddParameter(string paramName, object paramValue, SqlDbType dbType, int size)
        {
            SqlParameter p = new SqlParameter(paramName, dbType, size);
            p.Value = paramValue;
            p.Direction = ParameterDirection.Input;
            return p;
        }
        public SqlParameter AddParameter(string paramName, object paramValue, SqlDbType dbType, int size, ParameterDirection dir)
        {
            SqlParameter p = new SqlParameter(paramName, dbType, size);
            p.Value = paramValue;
            p.Direction = dir;
            return p;
        }

        private void AddParameters(SqlCommand cmd, List<SqlParameter> sqlParams)
        {
            foreach (SqlParameter param in sqlParams)
            {
                cmd.Parameters.Add(param);
            }
        }

        private SqlCommand PrepareCommand(string query, CommandType cmdType)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = query;
            cmd.CommandType = cmdType;
            cmd.Connection = Connection;
            cmd.CommandTimeout = 0;
            return cmd;
        }

        public SqlParameter AddParameter(string p1, ref int p2, SqlDbType sqlDbType, int p3, ParameterDirection parameterDirection)
        {
            throw new NotImplementedException();
        }
    }
}